/*     */ package com.newcrm.db;
/*     */ 
/*     */ import com.newcrm.exception.DBException;
/*     */ import com.newcrm.util.Util;
/*     */ import java.sql.Connection;
/*     */ import java.sql.SQLException;
/*     */ import java.util.ArrayList;
/*     */ import java.util.Date;
/*     */ import java.util.List;
/*     */ import java.util.Map;
/*     */ import org.apache.commons.dbutils.DbUtils;
/*     */ import org.apache.commons.dbutils.MyMapListHandler;
/*     */ import org.apache.commons.dbutils.QueryRunner;
/*     */ import org.apache.commons.dbutils.handlers.MapHandler;
/*     */ import org.apache.commons.dbutils.handlers.ScalarHandler;
/*     */ import org.apache.log4j.Logger;
/*     */ 
/*     */ public class VOIPCallManager
/*     */ {
/*  29 */   private static Logger logger = Logger.getLogger(VOIPCallManager.class
/*  30 */     .getName());
/*     */ 
/*     */   public int[] add(Connection conn, List<Object[]> baseParams)
/*     */   {
/*  39 */     if (conn == null) {
/*  40 */       return null;
/*     */     }
/*     */     try
/*     */     {
/*  44 */       QueryRunner runner = new QueryRunner();
/*  45 */       return runner.batch(conn, "INSERT INTO T_VOIP_CALL_Info (CallId, UserId, Telephone, CallType, BegingDate, EndDate, TotalTime, FileUrl, LocalFileUrl, CompanyId, CreateDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE UserId = ? ", 
/*  46 */         (Object[][])baseParams.toArray(new Object[0][]));
/*     */     } catch (SQLException e) {
/*  48 */       logger.warn("create default word failed", e);
/*     */     }
/*  50 */     return null;
/*     */   }
/*     */ 
/*     */   public int addCallLog(Connection conn, Date startDate, Date endDate, int num, int companyId)
/*     */   {
/*  64 */     if (conn == null) {
/*  65 */       return 0;
/*     */     }
/*     */ 
/*  68 */     if ((startDate == null) || (endDate == null)) {
/*  69 */       return 0;
/*     */     }
/*     */     try
/*     */     {
/*  73 */       QueryRunner runner = new QueryRunner();
/*  74 */       return runner.update(conn, "INSERT INTO T_VOIP_CALL_LOG_Info (StartDate, EndDate, Num, CompanyId, CreateDate) values (?, ?, ?, ?, NOW())", new Object[] { startDate, 
/*  75 */         endDate, Integer.valueOf(num), Integer.valueOf(companyId) });
/*     */     } catch (SQLException e) {
/*  77 */       logger.warn("add CallLog failed", e);
/*     */     }
/*  79 */     return 0;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getDownloadUrl()
/*     */   {
/*  90 */     Connection conn = ConnectionManager.getInstance().getConnection();
/*  91 */     if (conn == null) {
/*  92 */       return null;
/*     */     }
/*     */ 
/*  95 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/*  97 */       return (List)runner.query(conn, "SELECT VOIPId, Telephone, FileUrl FROM T_VOIP_CALL_Info WHERE LocalFileUrl = '' LIMIT 0, 10", 
/*  98 */         new MyMapListHandler());
/*     */     } catch (SQLException e) {
/* 100 */       logger.warn("get download list failed", e);
/*     */     } finally {
/*     */       try {
/* 103 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 105 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 108 */     return null;
/*     */   }
/*     */ 
/*     */   public int updateFilePath(int id, String path)
/*     */   {
/* 119 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 120 */     if (conn == null) {
/* 121 */       return 0;
/*     */     }
/*     */ 
/* 124 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 126 */       return runner.update(conn, "UPDATE T_VOIP_CALL_Info SET LocalFileUrl = ? WHERE VOIPId = ?", new Object[] { 
/* 127 */         path, Integer.valueOf(id) });
/*     */     } catch (SQLException e) {
/* 129 */       logger.warn("update download file failed", e);
/*     */     } finally {
/*     */       try {
/* 132 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 134 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 137 */     return 0;
/*     */   }
/*     */ 
/*     */   public Date getLastSynchronousTime(int companyId)
/*     */   {
/* 147 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 148 */     if (conn == null) {
/* 149 */       return null;
/*     */     }
/* 151 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 154 */       Map result = (Map)runner.query(conn, 
/* 155 */         "SELECT MAX(EndDate) AS MAXEndDate FROM T_VOIP_CALL_LOG_Info WHERE CompanyId = ? ", new MapHandler(), new Object[] { 
/* 156 */         Integer.valueOf(companyId) });
/*     */ 
/* 158 */       if (result == null) {
/* 159 */         return null;
/*     */       }
/* 161 */       return (Date)result.get("MAXEndDate");
/*     */     } catch (SQLException e) {
/* 163 */       logger.warn("get company last synchronoustime failed", e);
/*     */     } finally {
/*     */       try {
/* 166 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 168 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 171 */     return null;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getCallRecords(String telephones, int companyId)
/*     */   {
/* 183 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 184 */     if (conn == null) {
/* 185 */       return null;
/*     */     }
/*     */ 
/* 188 */     if ((telephones == null) || (telephones.trim().length() == 0)) {
/* 189 */       return new ArrayList();
/*     */     }
/*     */ 
/* 192 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 194 */       return (List)runner.query(
/* 195 */         conn, 
/* 196 */         "SELECT UserId,Telephone,BegingDate,EndDate,TotalTime,FileUrl,LocalFileUrl FROM T_VOIP_CALL_Info WHERE CompanyId = ? AND Telephone IN (" + 
/* 197 */         Util.getStringWithQuotes(telephones) + 
/* 198 */         ") ORDER BY BegingDate DESC", 
/* 199 */         new MyMapListHandler(), new Object[] { Integer.valueOf(companyId) });
/*     */     } catch (SQLException e) {
/* 201 */       logger.warn("get Call Records list failed", e);
/*     */     } finally {
/*     */       try {
/* 204 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 206 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 209 */     return null;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getCallRecordsTelephoneTotalNum(String groupIds, int companyId, int seconds, Date startDate, Date endDate)
/*     */   {
/* 221 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 222 */     if (conn == null) {
/* 223 */       return null;
/*     */     }
/*     */ 
/* 226 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 228 */       StringBuffer sql = new StringBuffer();
/* 229 */       List params = new ArrayList();
/* 230 */       sql.append("SELECT COUNT(Telephone) AS totalNum, UserId FROM T_VOIP_CALL_Info WHERE CompanyId = ? ");
/* 231 */       params.add(Integer.valueOf(companyId));
/*     */ 
/* 233 */       if (seconds > 0)
/*     */       {
/* 235 */         sql.append(" AND TotalTime >= ? ");
/* 236 */         params.add(Integer.valueOf(seconds));
/*     */       }
/*     */       List localList1;
/* 239 */       if ((groupIds == null) || (groupIds.trim().length() == 0))
/*     */       {
/* 241 */         if (startDate != null) {
/* 242 */           sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 243 */           params.add(startDate);
/*     */         }
/* 245 */         if (endDate != null) {
/* 246 */           sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 247 */           params.add(endDate);
/*     */         }
/* 249 */         sql.append(" GROUP BY UserId");
/* 250 */         return (List)runner.query(conn, sql.toString(), 
/* 251 */           new MyMapListHandler(), params.toArray());
/*     */       }
/*     */ 
/* 254 */       if (startDate != null) {
/* 255 */         sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 256 */         params.add(startDate);
/*     */       }
/* 258 */       if (endDate != null) {
/* 259 */         sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 260 */         params.add(endDate);
/*     */       }
/*     */ 
/* 263 */       sql.append(" AND UserId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId IN (" + 
/* 264 */         groupIds + ")) GROUP BY UserId");
/* 265 */       params.add(Integer.valueOf(companyId));
/* 266 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 267 */         params.toArray());
/*     */     }
/*     */     catch (SQLException e) {
/* 270 */       logger.warn("get Call Records list failed", e);
/*     */     } finally {
/*     */       try {
/* 273 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 275 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 278 */     return null;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getCallRecordsTotalTime(String groupIds, int companyId, int seconds, Date startDate, Date endDate)
/*     */   {
/* 289 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 290 */     if (conn == null) {
/* 291 */       return null;
/*     */     }
/*     */ 
/* 294 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 296 */       StringBuffer sql = new StringBuffer();
/* 297 */       List params = new ArrayList();
/* 298 */       sql.append("SELECT SUM(TotalTime) AS totalNum, UserId FROM T_VOIP_CALL_Info WHERE CompanyId = ? ");
/* 299 */       params.add(Integer.valueOf(companyId));
/*     */ 
/* 301 */       if (seconds > 0)
/*     */       {
/* 303 */         sql.append(" AND TotalTime >= ? ");
/* 304 */         params.add(Integer.valueOf(seconds));
/*     */       }
/*     */       List localList1;
/* 307 */       if ((groupIds == null) || (groupIds.trim().length() == 0)) {
/* 308 */         if (startDate != null) {
/* 309 */           sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 310 */           params.add(startDate);
/*     */         }
/* 312 */         if (endDate != null) {
/* 313 */           sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 314 */           params.add(endDate);
/*     */         }
/*     */ 
/* 317 */         sql.append(" GROUP BY UserId");
/* 318 */         return (List)runner.query(conn, sql.toString(), 
/* 319 */           new MyMapListHandler(), params.toArray());
/*     */       }
/*     */ 
/* 322 */       if (startDate != null) {
/* 323 */         sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 324 */         params.add(startDate);
/*     */       }
/* 326 */       if (endDate != null) {
/* 327 */         sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 328 */         params.add(endDate);
/*     */       }
/*     */ 
/* 331 */       sql.append(" AND UserId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId IN (" + 
/* 332 */         groupIds + ")) GROUP BY UserId");
/* 333 */       params.add(Integer.valueOf(companyId));
/* 334 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 335 */         params.toArray());
/*     */     } catch (SQLException e) {
/* 337 */       logger.warn("get CallRecordsTotalTime failed", e);
/*     */     } finally {
/*     */       try {
/* 340 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 342 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 345 */     return null;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> listAll(String phone, String userId, String beginDate, int offset, int pageSize, int companyId)
/*     */     throws DBException, SQLException
/*     */   {
/* 361 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 362 */     if (conn == null) {
/* 363 */       return null;
/*     */     }
/*     */ 
/* 366 */     QueryRunner runner = new QueryRunner();
/* 367 */     List params = new ArrayList();
/* 368 */     params.add(Integer.valueOf(companyId));
/*     */ 
/* 370 */     StringBuffer sql = new StringBuffer();
/* 371 */     sql.append("SELECT UserId, Telephone, BegingDate, TotalTime, FileUrl, LocalFileUrl,CallType FROM T_VOIP_CALL_Info WHERE CompanyId = ? ");
/*     */ 
/* 374 */     if ((phone != null) && (phone.trim().length() > 0)) {
/* 375 */       sql.append(" AND Telephone like ? ");
/* 376 */       params.add("%" + phone + "%");
/*     */     }
/*     */ 
/* 380 */     if ((userId != null) && (userId.trim().length() > 0)) {
/* 381 */       sql.append(" AND UserId = ? ");
/* 382 */       params.add(userId);
/*     */     }
/*     */     int len;
/* 386 */     if ((beginDate != null) && (beginDate.trim().length() > 0)) {
/* 387 */       String[] arr = beginDate.split("~");
/* 388 */       len = arr.length;
/* 389 */       if (len >= 1) {
/* 390 */         Date startDate = Util.str2Date(arr[0]);
/* 391 */         Date endDate = null;
/* 392 */         if (len > 1) {
/* 393 */           endDate = Util.str2Date(arr[1]);
/*     */         }
/* 395 */         if (startDate != null) {
/* 396 */           sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 397 */           params.add(startDate);
/*     */         }
/* 399 */         if (endDate != null) {
/* 400 */           sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 401 */           params.add(endDate);
/*     */         }
/*     */       }
/*     */     }
/*     */ 
/* 406 */     sql.append(" ORDER BY BegingDate DESC ");
/* 407 */     sql.append(" LIMIT ").append(offset).append(", ").append(pageSize);
/*     */     try
/*     */     {
/* 410 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 411 */         params.toArray());
/*     */     } finally {
/*     */       try {
/* 414 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 416 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int listAllCount(String phone, String userId, String beginDate, int companyId)
/*     */     throws DBException, SQLException
/*     */   {
/* 431 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 432 */     if (conn == null) {
/* 433 */       return 0;
/*     */     }
/* 435 */     QueryRunner runner = new QueryRunner();
/* 436 */     StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM T_VOIP_CALL_Info WHERE CompanyId = ? ");
/* 437 */     List params = new ArrayList();
/* 438 */     params.add(Integer.valueOf(companyId));
/*     */ 
/* 441 */     if ((phone != null) && (phone.trim().length() > 0)) {
/* 442 */       sql.append(" AND Telephone like ? ");
/* 443 */       params.add("%" + phone + "%");
/*     */     }
/*     */ 
/* 447 */     if ((userId != null) && (userId.trim().length() > 0)) {
/* 448 */       sql.append(" AND UserId = ? ");
/* 449 */       params.add(userId);
/*     */     }
/*     */     Date startDate;
/* 453 */     if ((beginDate != null) && (beginDate.trim().length() > 0)) {
/* 454 */       String[] arr = beginDate.split("~");
/* 455 */       int len = arr.length;
/* 456 */       if (len >= 1) {
/* 457 */         startDate = Util.str2Date(arr[0]);
/* 458 */         Date endDate = null;
/* 459 */         if (len > 1) {
/* 460 */           endDate = Util.str2Date(arr[1]);
/*     */         }
/* 462 */         if (startDate != null) {
/* 463 */           sql.append(" AND DATEDIFF(BegingDate, ?) >= 0 ");
/* 464 */           params.add(startDate);
/*     */         }
/* 466 */         if (endDate != null) {
/* 467 */           sql.append(" AND DATEDIFF(BegingDate, ?) <= 0 ");
/* 468 */           params.add(endDate);
/*     */         }
/*     */       }
/*     */     }
/*     */ 
/*     */     try
/*     */     {
/* 475 */       Object obj = runner.query(conn, sql.toString(), 
/* 476 */         new ScalarHandler(1), params.toArray());
/* 477 */       if (!(obj instanceof Long)) {
/* 478 */         return 0;
/*     */       }
/* 480 */       return ((Long)obj).intValue();
/*     */     } finally {
/*     */       try {
/* 483 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 485 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getCustomerByTelephone(String telephones, int companyId)
/*     */     throws DBException, SQLException
/*     */   {
/* 497 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 498 */     if (conn == null) {
/* 499 */       return null;
/*     */     }
/*     */ 
/* 502 */     QueryRunner runner = new QueryRunner();
/* 503 */     List params = new ArrayList();
/* 504 */     params.add(Integer.valueOf(companyId));
/* 505 */     params.add(Integer.valueOf(1));
/*     */ 
/* 507 */
                StringBuffer sql = new StringBuffer();
/*     */     String tel;
/* 509 */     if (telephones.indexOf(",") > 0)
/*     */     {
/* 513 */       StringBuffer tels = new StringBuffer();
                for(int tNum = 0;tNum<(telephones.split(",")).length;tNum++){
                    tel = (telephones.split(","))[tNum];
/* 515 */         if (tels.length() == 0)
/* 516 */           tels = tels.append("'");
/*     */         else {
/* 518 */           tels = tels.append(",'");
/*     */         }
/* 520 */         tels.append(tel).append("'");
/*     */       }
/* 522 */       sql.append("SELECT A.Info1, A.CustomerId, B.Name, B.Type, C.Name AS ParentName from T_Customer_Ext_Info AS A INNER JOIN T_Customer_Info AS B ON A.CustomerId = B.CustomerId LEFT JOIN T_Customer_Info AS C ON B.ParentCustomerId = C.CustomerId WHERE A.CompanyId = ? and A.Type = ? And A.Info1 IN ( ")
/* 523 */         .append(tels.toString()).append(")");
/*     */     }
/*     */     else {
/* 526 */       sql.append("SELECT A.Info1, A.CustomerId, B.Name, B.Type, C.Name AS ParentName from T_Customer_Ext_Info AS A INNER JOIN T_Customer_Info AS B ON A.CustomerId = B.CustomerId LEFT JOIN T_Customer_Info AS C ON B.ParentCustomerId = C.CustomerId WHERE A.CompanyId = ? and A.Type = ? And A.Info1 = ? ");
/* 527 */       params.add(telephones);
/*     */     }
/*     */     try
/*     */     {
/* 531 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 532 */         params.toArray());
/*     */     } finally {
/*     */       try {
/* 535 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 537 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ }

/* Location:           C:\E\referenceProject\CRM参考\qikebao\WEB-INF\lib\qikebao.jar
 * Qualified Name:     com.newcrm.db.VOIPCallManager
 * JD-Core Version:    0.6.1
 */